<?php
// +----------------------------------------------------------------------
// | Site:  [ http://www.yzmcms.com]
// +----------------------------------------------------------------------
// | Copyright: 袁志蒙工作室，并保留所有权利
// +----------------------------------------------------------------------
// | Author: YuanZhiMeng <214243830@qq.com>
// +---------------------------------------------------------------------- 
// | Explain: 这不是一个自由软件,您只能在不用于商业目的的前提下对程序代码进行修改和使用，不允许对程序代码以任何形式任何目的的再发布！
// +----------------------------------------------------------------------

defined('IN_YZMPHP') or exit('Access Denied'); 
yzm_base::load_controller('common', 'admin', 0);
yzm_base::load_sys_class('page','',0);
yzm_base::load_common('class/jst.class.php');
yzm_base::load_common('class/xlswriter.class.php');

class jst_inventorys extends common {

	/**
	 * 配置信息
	 */

	public $config=[];
	public $data=[];
	public $jst=[];
	public $jst_arr=[];
	public function __construct(){
		$this->jst = get_jst_list();
		$k = 0;
		foreach($this->jst as $v){
			$this->jst_arr[$k]['id'] = $v['id'];
			$this->jst_arr[$k]['title'] = $v['short_name'];
			$k++;
		}
		parent::__construct();
	}
	
	public function where(){
		$where = '1=1';
		$post = $_POST;
		$i_id =$post["i_id"] ?? '';
		$sku_id = $post["sku_id"] ?? '';
		$brand = isset($post["brand"]) ? $post["brand"] : '';
		$time_type = $post["time_type"] ?? 0;
		$properties_value = isset($post['properties_value']) ? safe_replace(trim($post['properties_value'])) : '';
		$name = isset($post["name"]) ? $post["name"] : '';
		$labels = isset($post['labels']) ? $post['labels'] : '';
		$other_2 = $post["other_2"] ?? '';
		if($i_id) {
			if(strpos($i_id,',')){
				$i_id = str_replace(',','","',$i_id);
				$where .= ' AND s.i_id in("'.$i_id.'")';
			}else{
				if($i_id != trim($i_id,'*')){
					$i_id = trim($i_id,'*');
					$where .= ' AND s.i_id LIKE \'%'.$i_id.'%\'';
				}else{
					$where .= ' AND s.i_id = "'.$i_id.'"';
				}
			}
		}
		if($sku_id) {
			if(strpos($sku_id,',')){
				$sku_id = str_replace(',','","',$sku_id);
				$where .= ' AND s.sku_id in("'.$sku_id.'")';
			}else{
				if($sku_id != trim($sku_id,'*')){
					$sku_id = trim($sku_id,'*');
					$where .= ' AND s.sku_id LIKE \'%'.$sku_id.'%\'';
				}else{
					$where .= ' AND s.sku_id = "'.$sku_id.'"';
				}
			}
		}
		if($brand){
			$brand = str_replace(',','","',$brand);
			$where .= ' AND s.brand in("'.$brand.'")';
		}
		if($properties_value) {
			$where .= ' AND s.properties_value LIKE \'%'.$properties_value.'%\'';
		}
		if($name) {
			$where .= ' AND s.name LIKE \'%'.$name.'%\'';
		}
		if($other_2) {
			if(strpos($other_2,',')){
				$other_2 = str_replace(',','","',$other_2);
				$where .= ' AND s.other_2 in("'.$other_2.'")';
			}else{
				$where .= ' AND s.other_2 LIKE \'%'.$other_2.'%\'';
			}
		}
		if($labels){
			$labels_arr = explode(',',$labels);
			foreach($labels_arr as $label){
				$where .= ' AND s.labels like "%'.$label.'%"';
			}
		}
		$ups = $post["ups"] ?? '';
		if($ups){
			//百分号转义
			$ups = str_replace("%", "\%", $ups);
			$where .=' and su.pv_value like "%'.$ups.'%"';
		}
		if(isset($post['is_ys'])&&$post['is_ys']==1){//是否只筛选佑晟公司品牌
			$brand_ys = implode(',',get_brand_list_ys());
			$brand_ys = str_replace(',','","',$brand_ys);
			$where .= ' AND s.brand in("'.$brand_ys.'")';
		}
		if(isset($post['category'])&&trim($post['category'])!=''){
			$where .= ' AND s.category = "'.$post['category'].'"';
		}
		
		$color = isset($post["color"]) ? $post["color"] : '';
		if($color){
			$where .=' and SUBSTRING_INDEX( s.properties_value, ";", 1) like "%'.$color.'%"';
		}
		$remark = $post["remark"] ?? '';
		if($remark){
			$where .=" and s.remark like '%".$remark."%'";
		}		
		return $where;
	}
	
	public function inventory_list() {
		$of = input('post.field') ? input('post.field') : 'modified';
		$or = input('post.order') ? input('post.order') : 'desc';
		$download = input('post.download') ? input('post.download') : 0;
		$d = D('jst_inventorys');
		$where = $this->where();
		$page = input('post.page');
		$limit = input('post.limit');
		$error_type = ['未知','正常','库存过多','未上线','未加虚拟'];
		
		if($page&&$download==0){
			$total = $d->alias('i')->where($where)->join(C('db_prefix').'jst_sku s on s.sku_id = i.sku_id and s.jst_id2 = i.jst_id2','left')->total();
			$qty_sum = $d->alias('i')->field('sum(qty) qty_sum')->join(C('db_prefix').'jst_sku s on s.sku_id = i.sku_id and s.jst_id2 = i.jst_id2','left')->where($where)->one();
			$page = new page($total, $limit);
			$data = $d->alias('i')->field('i.*,s.pic,s.other_2,s.name,s.properties_value,s.sku_code,s.enabled,s.i_id,s.labels')->join(C('db_prefix').'jst_sku s on s.sku_id = i.sku_id and s.jst_id2 = i.jst_id2','left')->where($where)->limit($page->limit())->order("i.$of $or")->select();
			foreach($data as &$v){
				$v['jst_name'] = $this->jst[$v['jst_id2']]['short_name'];
				$v['error_type'] = $error_type[$v['error_type']];
			}
			$data = ['code'=>0,'msg'=>'','count'=>$total,'data'=>$data,'qty_sum'=>$qty_sum?$qty_sum:0];
			return_json($data);
		}
		if($download==1){
			$total = $d->alias('i')->where($where)->join(C('db_prefix').'jst_sku s on s.sku_id = i.sku_id and s.jst_id2 = i.jst_id2','left')->total();
			$number = 50000;
			if($total>50000){
				$data = ['code'=>1,'msg'=>'单次最多导出5万条记录','url'=>''];
				return_json($data);
			}
			$page = ceil($total/$number);
			$excel = new xlswriter('inventorys',1);
			$fields = excel_need_field('inventorys');
			foreach($fields as &$v){
				if($v=='i_id'){
					$v= 's.i_id';
				}else{
					$v = 'i.'.$v;
				}
			}
			$field = implode(',',$fields);
			$data =$d->alias('i')->field($field)->join(C('db_prefix').'jst_sku s on s.sku_id = i.sku_id and s.jst_id2 = i.jst_id2','left')->where($where)->order("i.$of $or")->select();
			$excel->insert_data($data);
			unset($data);
			$url = $excel->output();
			$data = ['code'=>0,'msg'=>'','url'=>$url];
			return_json($data);
		}else{
			include $this->admin_tpl('jst_inventorys/inventory_list');
		}
	}
	
	/*箱及库位*/
	public function pack_query(){
		$of = input('post.field') ? input('post.field') : 'modified';
		$or = input('post.order') ? input('post.order') : 'desc';
		$d = D('jst_inventorys_sku');
		$where = $this->where();
		$page = input('post.page');
		$limit = input('post.limit');
		if($page){
			$post = $_POST;
			$pack_type = $post["pack_type"] ?? 'Bin';
			$wh_names = [1=>'主仓',2=>'销退仓',3=>'进货仓',4=>'次品仓',5=>'门店',6=>'自定义1仓',7=>'自定义2仓',8=>'自定义3仓',9=>'自定义4仓',10=>'自定义5仓',0=>'错误的仓库',1000=>'外仓',-1=>'已出库',-2=>'待进仓',];
			if($pack_type == 'Bin'){
				$where .= ' and qty>0';
			}elseif($pack_type == 'DefaultPack'){
				$where .= ' and (in_qty+qty+return_qty+defective_qty)>0';
			}
			
			$total = $d->alias('s')->where($where)->total();
			$qty_sum = $d->alias('s')->field('sum(qty) qty_sum')->where($where)->one();
			$page = new page($total, $limit);
			$select_data = $d->alias('s')->where($where)->limit($page->limit())->order("s.$of $or")->select();
			if($select_data){
				$list = [];
				$sku_list = [];
				foreach($select_data as $v){
					$list[$v['jst_id2']][] = $v['sku_id']; 
					$sku_list[$v['sku_id']] = $v;
				}
				foreach($list as $k => $v){
					$jst_id = $k;
					$jst = new jst($jst_id);
					$result = $jst->pack_query(['sku_ids'=>$v,'pack_type'=>$pack_type]);
					$msg = '';
					if($result['code']==0){
						//处理数据
						$result = $result['data']['datas'];
						$data = [];
						foreach($result as $val){
							$temp = $sku_list[$val['sku_id']];
							$temp['wms_name'] = get_wms_name($val['wms_co_id']);
							$temp['wms_co_id'] = $val['wms_co_id'];
							$temp['item_bin'] = $val['item_bin']; 
							$temp['pack_type'] = $val['pack_type']; 
							$temp['pack_id'] = $val['pack_id']; 
							$temp['wh_id'] = $val['wh_id']; 
							$temp['bin'] = $val['bin'];
							$temp['qty'] = $val['qty'];
							$temp['modified'] = $val['modified'];
							$temp['jst_name'] = $this->jst[$k]['short_name'];
							$temp['wh_name'] = $wh_names[$val['wh_id']];
							$data[] = $temp;
						}
						$data = ['code'=>0,'msg'=>'','count'=>$total,'data'=>$data,'qty_sum'=>$qty_sum?$qty_sum:0];
						return_json($data);
					}else{
						$msg .= ',接口错误信息：'.$result['msg'];
						$data = ['code'=>1,'msg'=>$msg];
						return_json($data);
					}
					echo $msg.PHP_EOL;
				}
			}
		}else{
			include $this->admin_tpl('jst_inventorys/pack_query');
		}
	}
	
	//唯品补货
	public function wp_buhuo(){
		$type = isset($_GET['type']) ? $_GET['type'] : '';
		if($type == 'touwei'){//头尾码设置列表
			$data = D('jst_sku_touwei')->order('chima_num asc')->select();
			$tou = $data[0];
			$wei = end($data);
			array_unshift($data,['chima_num'=>'<'.$tou['chima_num'],'tou_num'=>0,'wei_num'=>0,'remark'=>'均码不设置头尾码']);
			$data[] = ['chima_num'=>'>'.$wei['chima_num'],'tou_num'=>2,'wei_num'=>2,'remark'=>'尺码多头尾个2个尺码'];
			include $this->admin_tpl('jst_inventorys/jst_wp_buhuo_touwei');
			return;
		}else if($type == 'reset_touwei'){//重置头尾码
			delcache('LOCK::get_touwei');
			if(isset($_GET['error'])){
				$res = D('jst_sku')->update(['tou_wei'=>0],['tou_wei'=>-1]);
				if($res !== false){
					return_json(array('status'=>1,'message'=>'操作成功，将在后台执行重新计算异常头尾码'));
				}else{
					return_json(array('status'=>0,'message'=>L('operation_failure')));
				}	
			}else{
				$res = D('jst_sku')->update(['tou_wei'=>0],'1');
				if($res !== false){
					return_json(array('status'=>1,'message'=>'操作成功，将在后台执行重新计算头尾码,预计需要30分钟'));
				}else{
					return_json(array('status'=>0,'message'=>L('operation_failure')));
				}	
			}
		}else if($type == 'tag'){//标签列表
			$brand = isset($_GET['brand']) ? $_GET['brand'] : '';
			$where = [];
			if($brand){
				$where = "brand in ('全局','".$brand."')";
			}else{
				$where = "brand in ('全局','默认')";
			}
			$data = D('jst_sku_tag')->order('id asc')->where($where)->select();
			if(!$brand){
				include $this->admin_tpl('jst_inventorys/jst_wp_buhuo_tag');
				return;
			}
			$info =  D('jst_sku_tag')->order('id asc')->where(['brand'=>$brand])->select();
			if(!$info){
				$data = D('jst_sku_tag')->order('id asc')->where(['brand'=>'默认'])->select();
				foreach($data as $v){
					$v['brand'] = $brand;
					unset($v['id']);
					$insert_data[] = $v;
				}
				D('jst_sku_tag')->insert_all($insert_data);
				$data = D('jst_sku_tag')->order('id asc')->where($where)->select();
			}
			delcache('CONFIG::BUHUO:*');
			return_json(array('status'=>1,'data'=>$data));
		}else if($type == "tag_add"){//添加标签
			if(isset($_GET['dosubmit'])){
				$_GET["brand"] = str_replace('+',' ',$_GET["brand"]);
				$res = D('jst_sku_tag')->insert($_GET);
				if($res !== false){
					return_json(array('status'=>1,'message'=>L('operation_success')));
				}else{
					return_json(array('status'=>0,'message'=>L('operation_failure')));
				}
			}
			$info = ['tag_name'=>'','days_avg'=>'','id'=>'','days_num'=>'','remark'=>''];
			include $this->admin_tpl('jst_inventorys/jst_wp_buhuo_tag_add');
			return;
		}else if($type == "tag_edit"){//编辑标签
			$id = $_GET['id'];
			if(isset($_GET['dosubmit'])){
				$_GET["brand"] = str_replace('+',' ',$_GET["brand"]);
				$res = D('jst_sku_tag')->update($_GET,['id'=>$id]);
				delete_all('CONFIG::BUHUO*');
				if($res !== false){
					return_json(array('status'=>1,'message'=>L('operation_success')));
				}else{
					return_json(array('status'=>0,'message'=>L('operation_failure')));
				}
			}
			$info = D('jst_sku_tag')->where(['id'=>$id])->find();
			include $this->admin_tpl('jst_inventorys/jst_wp_buhuo_tag_add');
			return;
		}else if($type == "tag_del"){//删除标签
			$res = D('jst_sku_tag')->delete(['id'=>$_GET['id']]);
			delete_all('CONFIG::BUHUO*');
			if($res !== false){
				return_json(array('status'=>1,'message'=>L('operation_success')));
			}else{
				return_json(array('status'=>0,'message'=>L('operation_failure')));
			}
		}else if($type == "add_remark"){//添加补货备注
			$kuan = $_GET['kuan'];
			$info = D('jst_buhuo_remark')->field('*')->where(['kuan'=>$kuan])->find();
			if(!$info){
				$info['kuan'] = $_GET['kuan'];
				$info['remark'] = '';
				$info['id'] = '';
			}
			include $this->admin_tpl('jst_inventorys/jst_wp_buhuo_remark');
			exit;
		}else if($type == 'action_remark'){//提交补货备注
			$id = isset($_GET['id'])?$_GET['id']:0;
			//去除所有空白字符
			$remark = preg_replace("/\s|　/","",$_GET['remark']);
			$kuan = $_GET['kuan'];
			if($id){
				$res = D('jst_buhuo_remark')->update($_GET,['id'=>$id]);
			}else{
				$res = D('jst_buhuo_remark')->insert($_GET);
			}
			if($res){
				return_json(array('status'=>1,'message'=>'操作成功'));
			}else{
				return_json(array('status'=>0,'message'=>'添加失败'));
			}
		}else if($type == 'del_remark'){//删除补货备注
			if(D('jst_buhuo_remark')->delete(['id'=>$_GET['id']])){
				return_json(array('status'=>1,'message'=>'删除成功'));
			}else{
				return_json(array('status'=>0,'message'=>'删除失败'));
			}
		}
		
		
		$of = input('post.field') ? input('post.field') : '7days';
		$or = input('post.order') ? input('post.order') : 'desc';
		$download = isset($_POST['download']) ? $_POST['download'] : 0;
		$page = input('post.page')?input('post.page'):1;
		$limit = input('post.limit')?input('post.limit'):30;
		$this_page = $page;
		$this_limit = $limit;
		if(!input('post.yzm_csrf_token')&&$download == 0){
			return include $this->admin_tpl('jst_inventorys/wp_buhuo');
		}
		
		if($_POST['brand']==''||$_POST['brand']=='佑晟共享仓'||$_POST['brand']=='JEEP半成品'||$_POST['brand']=='马克华菲半成品'){
			return_json(['code'=>1,'msg'=>'请先选择有销量的品牌']);
		}
		$where = $this->where();
		$where .=' and s.7days>0';
		$sku = D('jst_sku_sales2');
		$total = $sku->alias('s')->field('count(distinct CONCAT(i_id,substring_index(properties_value,";",1))) count')->where($where)->find();
		$total = $total['count'];
		if($download==0){
			$page = new page($total, $limit);
			$data = $sku->alias('s')->field('s.i_id,s.pic,s.name,s.brand,s.category,substring_index(s.properties_value,";",1) color,s.labels,s.labels_brand,sum(s.1days) 1days,sum(s.2days) 2days,sum(s.3days) 3days,sum(s.5days) 5days,sum(s.7days) 7days,s.enabled,s.created,s.modified')->where($where)->limit($page->limit())->order("$of $or")->group('CONCAT(i_id,substring_index(properties_value,";",1))')->select();
			//print_r($data);exit;
			$i = 1;
			if($data){
				foreach($data as &$d){
					$d['id'] = ($this_page-1)*$this_limit+$i;
					$i++;
					//趋势
					$d['trend'] = avg_trend($d['3days']/3,$d['7days']/7);
					$d['qty'] = 0;
					$d['beihuo_qty'] = 0;
					$d['buhuo_qty'] = 0;
					$d['virtual_qty'] = 0;
					//日均
					$d['d'] = 0;
					//是否有备货仓
					$d['is_beihuo'] = 0;
					$brand_beihuo = brand_beihuo($d['brand'],$d['labels_brand']);
					$d['days_num'] = $brand_beihuo['days_num'];
					$d['days'] = $brand_beihuo['days'];
					//$d['days_num'] = brand_beihuo($d['brand'],$d['labels_brand']);
					$d['children'] = $sku->alias('s')
						->field('s.*,s.sku_id i_id,s.other_2 name,i.qty,i.virtual_qty,i.purchase_qty')				
						->join(C('db_prefix').'jst_inventorys i ON i.sku_id=s.sku_id', 'left')
						->where(['s.i_id'=>$d['i_id'],'substring_index(properties_value,";",1)'=>$d['color']])->order("s.other_2 asc,s.name asc")->select();
					$d['purchase_qty'] = 0;	
					foreach($d['children'] as &$sl){
						$sl['trend'] = avg_trend($sl['3days']/3,$sl['7days']/7);
						$sl['id'] = '↦';
						$sl['d'] = 0;
						if(isset($sl[$d['days'].'days'])){
							$sl['d'] = round($sl[$d['days'].'days']/$d['days']);
						}
						$sl['beihuo_qty'] = 0;
						$d['d'] += $sl['d'];
						//加工中
						$sl['purchase_qty'] = $sl['purchase_qty'];
						$d['purchase_qty'] += $sl['purchase_qty'];
						//补货天数
						$sl['days_num'] = $d['days_num'];
						$d['qty'] += $sl['qty'];
						//虚拟库存
						$d['virtual_qty'] += $sl['virtual_qty'];
						//头尾码
						$sl['touwei'] = touwei_code($sl['tou_wei']);
						if(strpos($sl['labels'],'备货仓')>-1){
							if($d['brand']=='JEEP'){
								$sl['beihuo_qty'] = D('jst_inventorys')->field('sum(qty)')->where(['other_2'=>$sl['other_2'],'brand'=>'JEEP半成品'])->one();
							}elseif($d['brand']!='法派FAPAI'){
								$sl['beihuo_qty'] = D('jst_inventorys')->field('sum(qty)')->where(['other_2'=>$sl['other_2'],'brand'=>'佑晟共享仓'])->one();
							}elseif($d['brand']=='马克华菲'){
								$sl['beihuo_qty'] = D('jst_inventorys')->field('sum(qty)')->where(['other_2'=>$sl['other_2'],'brand'=>'马克华菲半成品'])->one();
							}else{
								$sl['beihuo_qty'] = D('jst_inventorys')->field('sum(in_qty)')->where(['other_2'=>$sl['other_2']])->one();
							}
							$d['beihuo_qty'] += $sl['beihuo_qty'];
							$d['is_beihuo'] = 1;
						}
						//补货数量
						$sl['buhuo_qty'] = 0;
						if($d['days_num']>0){
							$need_buhuo = $sl['d']*$d['days_num']-$sl['purchase_qty'];
							//小销量款-，默认不补货
							if($sl['labels_brand'] == '小销量-'){
								$need_buhuo = 0;
							}else if($sl['labels_brand'] == '小销量+'){
								//小销量款+，头尾不补货
								if(in_array($sl['tou_wei'],[1,3])){
									$need_buhuo = 0;
								}else if($need_buhuo<3){
									//非头尾码至少需要补货到3件
									$need_buhuo = 3;
								}
							}else{
								if(in_array($sl['tou_wei'],[1,3])&&$need_buhuo<3){
									//头尾码至少需要补货到3件
									$need_buhuo = 3;
								}
							}
							$buhuo_qty = $need_buhuo-$sl['qty'];
							//补货1件时不补货
							if($buhuo_qty<2){
								$buhuo_qty = 0;
								continue;
							}
							//需要补两件是按3件补
							if($buhuo_qty == 2 || $buhuo_qty == 3){
								$sl['buhuo_qty'] = 3;
							}else{
								//库存大于8件，补货数量小于3件，无需补货
								if($sl['qty']>7&&$buhuo_qty<3){
									$sl['buhuo_qty'] = 0;
								}else{
									$sl['buhuo_qty'] = round($buhuo_qty/5)*5;
								}
							}
						}
						
						//补货库存大于备货仓，以备货仓库存为主
						/*
						if($sl['buhuo_qty']>$sl['beihuo_qty']){
							$sl['buhuo_qty'] = $sl['beihuo_qty'];
						}*/
						$d['buhuo_qty'] += $sl['buhuo_qty'];
					}
						
				}
			}
			$data = ['code'=>0,'msg'=>'','count'=>$total,'data'=>$data];
			return_json($data);
		}else{
			$number = 50000;
			$page = ceil($total/$number);
			$excel = new xlswriter('wp_buhuo',1);
			//插入excel的数据
			$excel_data = [];
			$data = $sku->alias('s')->field('i_id,pic,name,brand,category,substring_index(properties_value,";",1) color,labels,labels_brand,sum(1days) 1days,sum(2days) 2days,sum(3days) 3days,sum(5days) 5days,sum(7days) 7days,round(if((sum(3days/3)-sum(7days/7))/sum(3days/3)>=0.1,sum(3days/3),sum(7days/7)),0) as d,enabled,created,modified')->where($where)->order("$of $or")->group('CONCAT(i_id,substring_index(properties_value,";",1))')->select();
			//print_r($data);exit;
			$field = 's.i_id,s.sku_id,s.properties_value,s.name,s.other_2,s.brand,s.labels,s.labels_brand,i.qty,i.virtual_qty,s.1days,s.2days,s.3days,s.5days,s.7days,s.tou_wei,purchase_qty';
			if($data){
				foreach($data as $d){
					//趋势
					$d['trend'] = avg_trend($d['3days']/3,$d['7days']/7);
					//日均
					$d['d'] = 0;
					$brand_beihuo = brand_beihuo($d['brand'],$d['labels_brand']);
					$d['days_num'] = $brand_beihuo['days_num'];
					$d['days'] = $brand_beihuo['days'];
					$sku_list = $sku->alias('s')
						->field($field)				
						->join(C('db_prefix').'jst_inventorys i ON i.sku_id=s.sku_id', 'left')
						->where(['s.i_id'=>$d['i_id'],'substring_index(properties_value,";",1)'=>$d['color']])->order("s.other_2 asc,s.name asc")->select();
						//print_r($sku_list);
					foreach($sku_list as $sl){
						//备注
						$sl['remark'] = $d['remark']??'';
						$sl['d'] = 0;
						if(isset($sl[$d['days'].'days'])){
							$sl['d'] = round($sl[$d['days'].'days']/$d['days']);
						}
						$sl['beihuo_qty'] = 0;
						if(strpos($sl['labels'],'备货仓')){
							if($d['brand']!='法派FAPAI'){
								$sl['beihuo_qty'] = D('jst_inventorys')->field('sum(qty)')->where(['other_2'=>$sl['other_2']])->one();
							}else{
								$sl['beihuo_qty'] = D('jst_inventorys')->field('sum(in_qty)')->where(['other_2'=>$sl['other_2']])->one();
							}
						}
						$sl['i_id'] .= '('.$d['color'].')';
						$sl['labels'] = trim($sl['labels'].','.touwei_code($sl['tou_wei']),',');
						$sl['purchase_qty'] = $sl['purchase_qty'];
						//备货天数
						$sl['days_num'] = $d['days_num'];
						$d['d'] += $sl['d'];
						$sl['trend'] = $d['trend'];
						//补货数量
						$sl['buhuo_qty'] = 0;
						if($d['days_num']>0){
							$need_buhuo = $sl['d']*$d['days_num']-$sl['purchase_qty'];
							//小销量款-，默认不补货
							if($sl['labels_brand'] == '小销量-'){
								$need_buhuo = 0;
							}else if($sl['labels_brand'] == '小销量+'){
								//小销量款+，头尾不补货
								if(in_array($sl['tou_wei'],[1,3])){
									$need_buhuo = 0;
								}else if($need_buhuo<3){
									//非头尾码至少需要补货到3件
									$need_buhuo = 3;
								}
							}else{
								if(in_array($sl['tou_wei'],[1,3])&&$need_buhuo<3){
									//头尾码至少需要补货到3件
									$need_buhuo = 3;
								}
							}
							$buhuo_qty = $need_buhuo-$sl['qty'];
							//补货1件时不补货
							if($buhuo_qty<2){
								$buhuo_qty = 0;
								$sl['buhuo_qty'] = 0;
							}else{
								//无需补货
								if($buhuo_qty==1){
									$sl['buhuo_qty'] = 0;
								}else{
									//需要补两件是按3件补
									if($buhuo_qty == 2 || $buhuo_qty == 3){
										$sl['buhuo_qty'] = 3;
									}else{
										//库存大于8件，补货数量小于3件，无需补货
										if($sl['qty']>7&&$buhuo_qty<3){
											$sl['buhuo_qty'] = 0;
										}else if($sl['qty']>20&&$buhuo_qty<5){
											//库存大于15件，补货数量小于5件，无需补货
											$sl['buhuo_qty'] = 0;
										}else{
											$sl['buhuo_qty'] = round($buhuo_qty/5)*5;
										}
									}
								}
							}
						}
						$excel_data[] = $sl;
					}
				}
				//print_r($excel_data);exit;
			}
			$excel->insert_data($excel_data);
			unset($excel_data);
			$url = $excel->output();
			$data = ['code'=>0,'msg'=>'','url'=>$url];
			return_json($data);
		}
	}
	
	/*佑晟找款*/
	public function ys_zhaokuan(){
		$type = isset($_GET['type']) ? $_GET['type'] : '';
		//颜色输入款，预选列表
		if($type == 'get_color'){
			$field = 'SUBSTRING_INDEX( properties_value, ";", 1 )';
			$list = D('jst_sku')->field($field.' color')->where('properties_value<>"" and properties_value like "%'.$_GET['color'].'%"')->order('CHAR_LENGTH('.$field.') asc')->group($field)->select();
			if($list){
				return_json(array('status'=>1,'message'=>L('operation_success'),'data'=>$list));
			}else{
				return_json(array('status'=>0,'message'=>''));
			}
		}
		//输入框，面料成分
		if($type == 'get_ups'){
			$where = 'su.pv_value like "%'.trim($_GET['ups']).'%"';
			$brand = isset($_GET["brand"]) ? $_GET["brand"] : '';
			if($brand){
				$brand = str_replace(',','","',$brand);
				$where .= ' AND s.brand in("'.$brand.'")';
			}
			$list = D('jst_sku_ups')->alias('su')->join(C('db_prefix').'jst_sku s ON s.i_id=su.i_id', 'left')->where($where)->field('pv_value')->where($where)->order('CHAR_LENGTH(pv_value) asc')->group('su.pv_value')->select();
			if($list){
				return_json(array('status'=>1,'message'=>L('operation_success'),'data'=>$list));
			}else{
				return_json(array('status'=>0,'message'=>''));
			}
		}
		//输入框，商品资料备注
		if($type == 'get_remark'){
			$where = 'remark like "%'.trim($_GET['ups']).'%"';
			$brand = isset($_GET["brand"]) ? $_GET["brand"] : '';
			if($brand){
				$brand = str_replace(',','","',$brand);
				$where .= ' AND brand in("'.$brand.'")';
			}
			$list = D('jst_sku')->field('remark as pv_value')->where($where)->order('CHAR_LENGTH(remark) asc')->group('remark')->select();
			if($list){
				return_json(array('status'=>1,'message'=>L('operation_success'),'data'=>$list));
			}else{
				return_json(array('status'=>0,'message'=>''));
			}
		}
		//删除成份数据
		if($type == 'del'){
			D('jst_sku_ups')->delete(['id'=>$_GET['id']]);
		}
		//添加面料成份
		if($type == "add"){
			$id = $_GET['id'];
			$info = D('jst_sku_ups')->field('pv_value,ups_sku_name')->where(['id'=>$id])->find();
			include $this->admin_tpl('jst/jst_ys_zhaokuan_add');
			exit;
		}
		//提交添加面料成份
		if($type == 'action_add'){
			$id = $_GET['id'];
			//去除所有空白字符
			$pv_value = preg_replace("/\s|　/","",$_GET['pv_value']);
			$ups_sku_name = preg_replace("/\s|　/","",$_GET['ups_sku_name']);
			$sku_ups = D('jst_sku_ups')->field('i_id,pv_value')->where(['id'=>$id])->find();
			if($pv_value == $sku_ups['pv_value']){
				return_json(array('status'=>0,'message'=>'面料成份已经存在！'));
			}
			$insert_data = [
				'i_id' => $sku_ups['i_id'],
				'p_name' => '面料成份',
				'pv_value' => $pv_value,
				'ups_sku_name' => $ups_sku_name,
				'add_time' => time()
			];
			$res = D('jst_sku_ups')->insert($insert_data);
			if($res){
				return_json(array('status'=>1,'message'=>'操作成功'));
			}else{
				return_json(array('status'=>0,'message'=>'添加失败'));
			}
		}
		if(!isset($_POST['page'])){
			include $this->admin_tpl('jst_inventorys/jst_ys_zhaokuan');
			exit;
		}
		$of = input('post.field') ? input('post.field') : 'add_time';
		$or = input('post.order') ? input('post.order') : 'desc';
		$page = input('post.page')?input('post.page'):1;
		$limit = input('post.limit')?input('post.limit'):30;
		$this_page = $page;
		$this_limit = $limit;

		//$sku_ups = D('jst_sku_ups');
		$sku = D('jst_sku');
		if(isset($enabled)) {
			if($enabled != 99){
				$where .= ' AND s.enabled = '.$enabled;
			}
		}
		$where = $this->where();
		$total = $sku->alias('s')->field('count(DISTINCT CONCAT(s.name,SUBSTRING_INDEX( s.properties_value, ";", 1 ),su.pv_value))')->join(C('db_prefix').'jst_sku_ups su ON s.i_id=su.i_id', 'left')->where($where)->one();
		$page = new page($total, $limit);
		$data = $sku->alias('s')->field('s.i_id,s.jst_id,su.id,s.pic,s.sku_code,s.other_2,s.name,SUBSTRING_INDEX( s.properties_value, ";", 1) color,s.brand,s.category,su.pv_value,s.enabled,s.modified,s.add_time,su.add_time su_add_time,s.remark')->join(C('db_prefix').'jst_sku_ups su ON s.i_id=su.i_id', 'left')->where($where)->limit($page->limit())->group('CONCAT(s.NAME,SUBSTRING_INDEX(s.properties_value,";",1 ),su.pv_value)')->select();
		
		foreach($data as &$v){
			$v['jst_name'] = $this->jst[$v['jst_id']]['short_name'];
		}
		$data = ['code'=>0,'msg'=>'','count'=>$total,'data'=>$data];
		return_json($data);
	}
	
}